package pl.edu.agh.ki.ewywiadowka.server;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Random;

import pl.edu.agh.ki.ewywiadowka.client.Const;
import pl.edu.agh.ki.ewywiadowka.client.EWywiadowkaService;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.AbsenceRegisterMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.AttendantModifyMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.AttendantRegisterMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.AttendantRemoveMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.ClassModifyMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.ClassRemoveMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.StudentModifyMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.StudentRegisterMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.StudentRemoveMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.TeacherModifyMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.TeacherRegisterMsg;
import pl.edu.agh.ki.ewywiadowka.client.Const.Error.TeacherRemoveMsg;
import pl.edu.agh.ki.ewywiadowka.client.dto.AbsenceDTO;
import pl.edu.agh.ki.ewywiadowka.client.dto.AttendantDTO;
import pl.edu.agh.ki.ewywiadowka.client.dto.ClassDTO;
import pl.edu.agh.ki.ewywiadowka.client.dto.MarkDTO;
import pl.edu.agh.ki.ewywiadowka.client.dto.StudentDTO;
import pl.edu.agh.ki.ewywiadowka.client.dto.SubjectDTO;
import pl.edu.agh.ki.ewywiadowka.client.dto.SystemUserDTO;
import pl.edu.agh.ki.ewywiadowka.client.dto.TeacherDTO;
import pl.edu.agh.ki.ewywiadowka.client.dto.UserDTO;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

/**
 * The server side implementation of the RPC service.
 */
@SuppressWarnings("serial")
public class EWywiadowkaServiceImpl extends RemoteServiceServlet implements
		EWywiadowkaService {

	private HashMap<String, String> _secretaries = new HashMap<String, String>();
	private HashMap<String, LinkedList<MarkDTO>> _marks = new HashMap<String, LinkedList<MarkDTO>>();
	private LinkedList<MarkDTO> _finalMarks = new LinkedList<MarkDTO>();
	
	private LinkedList<AttendantDTO> _attendants = new LinkedList<AttendantDTO>();
	
	private LinkedList<ClassDTO> _classes = new LinkedList<ClassDTO>();
	private LinkedList<StudentDTO> _students = new LinkedList<StudentDTO>();
	private LinkedList<TeacherDTO> _teachers = new LinkedList<TeacherDTO>();
	private LinkedList<TeacherDTO> _educators = new LinkedList<TeacherDTO>();
	private LinkedList<TeacherDTO> _potentialEducators = new LinkedList<TeacherDTO>();
	
	private Connection connection;
	
	private void getConnection() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException
	{
		if(connection==null)
		{
			Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
			connection=DriverManager.getConnection("jdbc:db2://localhost:8000/BD209004",CredentialsHolder.login,CredentialsHolder.passwd);
		}
	}


	public String greetServer(String input) {
		String serverInfo = getServletContext().getServerInfo();
		String userAgent = getThreadLocalRequest().getHeader("User-Agent");
		return "Hello, " + input + "!<br><br>I am running " + serverInfo
				+ ".<br><br>It looks like you are using:<br>" + userAgent;
	}

	private boolean isEducator(TeacherDTO teacher) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		String query="SELECT nauczyciele_id FROM Wychowawcy WHERE nauczyciele_id="+teacher.id;
		ResultSet rs=stmt.executeQuery(query);
		if(!rs.next())
			return false;
		int id=rs.getInt(1);
		if(id != teacher.id)
			return false;
		return true;
	}
	
	/**
	 * Funkcja sluzaca do logowania
	 * 
	 * @param login string z loginem
	 * @param pass string z haslem
	 * @return atrybuty zwiazane z tym uzytkownikiem, jesli nie istnieje w systemie to UNKNOWN
	 */
	public Const.Attributes getAttributes(String login, String pass) {
//        String sha1val = "";
//        MessageDigest algorithm = null;
//        try
//        {
//            algorithm = MessageDigest.getInstance("SHA-1");
//        }
//        catch (NoSuchAlgorithmException nsae)
//        {
//        	return Const.Attributes.UNKNOWN;
//        }
//        byte[] defaultBytes = pass.getBytes();
//        algorithm.reset();
//        algorithm.update(defaultBytes);
//        byte messageDigest[] = algorithm.digest();
//        StringBuffer hexString = new StringBuffer();
//        for (int i = 0; i < messageDigest.length; i++)
//        {
//            String hex = Integer.toHexString(0xFF & messageDigest[i]);
//            if (hex.length() == 1)
//            {
//                hexString.append('0');
//            }
//            hexString.append(hex);
//        }
//        sha1val = hexString.toString();
		String sha1val=sha1(pass);
        String query="SELECT uprawnienia FROM UzytkownicySystemu WHERE login='"+login+"' AND haslo='"+sha1val+"'";
        System.out.println(pass+" "+query);
		Statement stmt;
		String uprawnienia=null;
		try {
			getConnection();
			stmt = connection.createStatement();
			ResultSet rs=stmt.executeQuery(query);
			if(!rs.next()) {
				System.err.println("Nie ma takiego loginu w bazie ("+login+")");
				return Const.Attributes.UNKNOWN;
			}
			uprawnienia=rs.getString(1);
			//connection.close();
			//connection=null;
		} catch (Exception e) {
			System.err.println(e.getMessage());
			return Const.Attributes.UNKNOWN;
		}

		System.out.println("Uprawnienia "+uprawnienia+"|");
		if(uprawnienia == null)
			return Const.Attributes.UNKNOWN;
		else if(uprawnienia.equals("sekretarz "))
			return Const.Attributes.SECRETARY;
		else if(uprawnienia.equals("uczen     "))
			return Const.Attributes.STUDENT;
		else if(uprawnienia.equals("opiekun   "))
			return Const.Attributes.ATTENDANT;
		else if(uprawnienia.equals("nauczyciel"))
			return Const.Attributes.TEACHER;
		else if(uprawnienia.equals("wychowawca"))
			return Const.Attributes.EDUCATOR;
		return Const.Attributes.UNKNOWN;
	}

	/**
	 * Rejestracja klasy
	 * 
	 * @param clas klasa, ktora ma byc zarejestrowana w systemie
	 * @return kod bledu
	 */
	public Const.Error.ClassRegisterMsg registerClass(ClassDTO clas) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.ClassRegisterMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("INSERT INTO Klasy (rocznik, litera, wychowawca) VALUES (");
		query.append("'"+(clas.annual.getYear()+1900)+"-01-01', ");
		query.append("'"+clas.letter+"', ");
		query.append(clas.educator.id);
		query.append(")");
		System.out.println(query);
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
			if(rowCount == 0) {
				System.out.println("Nie powiodło się executeUpdate");
				return Const.Error.ClassRegisterMsg.UNKNOWN;
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			System.err.println(e.getSQLState());
			if(e.getSQLState().equals("23505"))
				return Const.Error.ClassRegisterMsg.CLASS_ALREADY_EXISTS;
			return Const.Error.ClassRegisterMsg.UNKNOWN;
		}
		return Const.Error.ClassRegisterMsg.OK;
	}
	
	/**
	 * Usuniecie klasy
	 * 
	 * @param clas klasa, ktora ma byc usunieta
	 * @return kod bledu
	 */
	public ClassRemoveMsg removeClass(ClassDTO clas) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.ClassRemoveMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("DELETE FROM Klasy WHERE rocznik=");
		query.append("'"+(clas.annual.getYear()+1900)+"-01-01' ");
		query.append("AND litera=");
		query.append("'"+clas.letter+"' ");
		query.append("AND wychowawca=");
		query.append(clas.educator.id);
		System.out.println(query.toString());
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
			if(rowCount == 0)
				return Const.Error.ClassRemoveMsg.UNKNOWN;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return Const.Error.ClassRemoveMsg.UNKNOWN;
		}
		return Const.Error.ClassRemoveMsg.OK;
	}

	/**
	 * Modyfikacja klasy, jej rocznika, litery, lub wychowawcy
	 * 
	 * @param clas
	 * @return kod bledu
	 */
	public ClassModifyMsg modifyClass(ClassDTO clas) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.ClassModifyMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("UPDATE Klasy SET rocznik=");
		query.append("'"+(clas.annual.getYear()+1900)+"-01-01', ");
		query.append("litera=");
		query.append("'"+clas.letter+"', ");
		query.append("wychowawca=");
		query.append(clas.educator.id);
		query.append(" WHERE id="+clas.id);
		System.out.println(query.toString());
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
			if(rowCount == 0)
				return Const.Error.ClassModifyMsg.UNKNOWN;
		} catch (SQLException e) {
			return Const.Error.ClassModifyMsg.UNKNOWN;
		}
		return Const.Error.ClassModifyMsg.OK;
	}
	
	/**
	 * Zwraca listę przedmiotów nauczanych przez danego nauczyciela
	 * @param id identyfikator nauczyciela
	 * @return lista przedmiotów
	 */
	private LinkedList<SubjectDTO> getSubjectsForTeacher(int id) {
		LinkedList<SubjectDTO> przedmioty=new LinkedList<SubjectDTO>();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT przedmioty_id, Przedmioty.nazwa FROM NauczanePrzedmioty JOIN Przedmioty ON przedmioty_id=przedmioty.id WHERE Nauczyciele_id="+id;
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				przedmioty.add(new SubjectDTO(rs.getInt(1),rs.getString(2)));
			}
		} catch (Exception e) {
			return null;
		}
		return przedmioty;
	}
	
	/**
	 * Odczytuje z bazy dane o uzytkowniku systemu (bez hasla) o danym identyfikatorze
	 * @param uprawnienia czy jest to nauczyciel, uczen, czy opiekun
	 * @param id identyfikator lub numer legitymacji
	 * @return uzytkownik systemu
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	private UserDTO getUser(Const.Attributes uprawnienia, int id) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		String query=null;
		if(uprawnienia==Const.Attributes.TEACHER || uprawnienia==Const.Attributes.EDUCATOR) {
			query="SELECT login, email FROM UzytkownicySystemu WHERE Nauczyciele_id="+id;
		} else if(uprawnienia==Const.Attributes.ATTENDANT) {
			query="SELECT login, email FROM UzytkownicySystemu WHERE Opiekunowie_id="+id;
		} else if(uprawnienia==Const.Attributes.STUDENT) {
			query="SELECT login, email FROM UzytkownicySystemu WHERE Uczniowie_nr_legitymacji="+id;
		}
		ResultSet rs=stmt.executeQuery(query);
		rs.next();
		return new UserDTO(rs.getString(1),null,rs.getString(2),uprawnienia);
	}
	
	/**
	 * Pobiera dane o nauczycielu o określonym id
	 * @return obiekt reprezentujący nauczyciela
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	private TeacherDTO getTeacher(int id) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		String query="SELECT id, nazwisko, imie, adres, telefon_kontaktowy FROM Nauczyciele WHERE id="+id;
		ResultSet rs=stmt.executeQuery(query);
		rs.next();
		return new TeacherDTO(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),getUser(Const.Attributes.TEACHER,id),getSubjectsForTeacher(id));
	}
	
	/**
	 * Pobiera dane o opiekunie o określonym id
	 * @return obiekt reprezentujący opiekuna
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	private AttendantDTO getAttendant(int id) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		String query="SELECT id, nazwisko, imie, adres, telefon, zaklad_pracy FROM Opiekunowie WHERE id="+id;
		ResultSet rs=stmt.executeQuery(query);
		rs.next();
		return new AttendantDTO(rs.getInt(1),rs.getString(3),rs.getString(2),rs.getString(4),rs.getString(5),rs.getString(6),getUser(Const.Attributes.ATTENDANT,id),getStudentsForAttendant(id));
	}
	
	/**
	 * Pobiera dane o uczniu o określonym id
	 * @return obiekt reprezentujący ucznia
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	private StudentDTO getStudent(int id) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		String query="SELECT nazwisko, imie, nr_legitymacji, adres, data_urodzenia, miejsce_urodzenia, klasy_id FROM Uczniowie WHERE nr_legitymacji="+id;
		ResultSet rs=stmt.executeQuery(query);
		rs.next();
		int uid=rs.getInt(3);
		java.sql.Date sdate=rs.getDate(5);
		Date date=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
		return new StudentDTO(rs.getString(1),rs.getString(2),uid,rs.getString(4),date, rs.getString(6),getAttendants(id),getExactClass(7));
	}
	
	/**
	 * @return lista wszystkich klas zarejestrowanych w systemie
	 */
	public LinkedList<ClassDTO> getClasses() {
       String query="SELECT id, litera, wychowawca, rocznik FROM Klasy";
       Statement stmt;
       _classes.clear();
       try {
			getConnection();
			stmt = connection.createStatement();
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				java.sql.Date sqldate=rs.getDate(4);
				Date date=new Date(sqldate.getYear(),sqldate.getMonth(),sqldate.getDay());
				String litera=rs.getString(2);
				_classes.add(new ClassDTO(rs.getInt(1),date,new Character(litera.charAt(0)),getTeacher(rs.getInt(3))));
			}
			//connection.close();
			//connection=null;
		} catch (Exception e) {
			return null;
		}
		return _classes;
	}

	/**
	 * zwraca wszystkich nauczycieli, ktorzy sa wychowawcami
	 * 
	 * @return lista nauczycieli nie bedacych wychowawcami
	 */
	public LinkedList<TeacherDTO> getEducators() {
		_educators.clear();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT nauczyciele_id, nazwisko, imie, adres, telefon FROM Wychowawcy";
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				int id=rs.getInt(1);
				_educators.add(new TeacherDTO(id,rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),getUser(Const.Attributes.EDUCATOR,id), getSubjectsForTeacher(id)));
			}
		} catch (Exception e) {
//			System.err.println(e.getMessage());
			return null;
		}
		return _educators;
	}
	
	/**
	 * zwraca wszystkich nauczycieli, ktorzy jeszcze nie sa wychowawcami
	 * 
	 * @return lista nauczycieli nie bedacych wychowawcami
	 */
	public LinkedList<TeacherDTO> getPotentialEducators() {
		_potentialEducators.clear();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT id, nazwisko, imie, adres, telefon FROM PotencjalniWychowawcy";
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				int id=rs.getInt(1);
				_potentialEducators.add(new TeacherDTO(id,rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),getUser(Const.Attributes.EDUCATOR,id), getSubjectsForTeacher(id)));
			}
		} catch (Exception e) {
//			System.err.println(e.getMessage());
			return null;
		}
		return _potentialEducators;
	}

	/**
	 * Zwraca liste opiekunow danego ucznia
	 * @param id numer legitymacji
	 * @return lista opiekunow
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	private LinkedList<AttendantDTO> getAttendants(int id) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		LinkedList<AttendantDTO> lista=new LinkedList<AttendantDTO>();
		getConnection();
		Statement stmt=connection.createStatement();
		String query="SELECT opiekun_imie,opiekun_nazwisko, opiekun_adres, opiekun_telefon, opiekun_zaklad_pracy, opiekun_id FROM UczniowieZOpiekunami WHERE nr_legitymacji="+id;
		ResultSet rs=stmt.executeQuery(query);
		while(rs.next())
			lista.add(new AttendantDTO(0,rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),getUser(Const.Attributes.ATTENDANT,rs.getInt(6)),null));
		return lista;
	}
	
	/**
	 * Funkcja zwraca sklad klasy
	 * 
	 * @param clas
	 * @return lista uczniow
	 */
	public LinkedList<StudentDTO> getClassSquad(ClassDTO clas) {
		LinkedList<StudentDTO> lista=new LinkedList<StudentDTO>();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT nr_legitymacji,imie,nazwisko, adres, miejsce_urodzenia, data_urodzenia,klasy_id  FROM SkladyKlas WHERE (YEAR(rocznik)="+clas.annual.getYear()+" OR YEAR(rocznik)="+(clas.annual.getYear()+1900)+") AND litera='"+clas.letter+"'";
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				int id=rs.getInt(1);
				clas.id=rs.getInt(7);
				java.sql.Date sdate=rs.getDate(6);
				Date date=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
				lista.add(new StudentDTO(rs.getString(3),rs.getString(2),rs.getInt(1),rs.getString(4),date,rs.getString(5),getAttendants(rs.getInt(1)),clas));
			}
		} catch (Exception e) {
//			System.err.println(e.getMessage());
			return null;
		}
		return lista;
	}
	
	/**
	 * Zwraca obiekt klasy (szkolnej ;P) o konkretnym id
	 * @param id identyfikator klasy
	 * @return klasa
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	private ClassDTO getExactClass(int id) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		String query="SELECT id, rocznik, litera, wychowawca FROM Klasy WHERE id="+id;
		ResultSet rs=stmt.executeQuery(query);
		rs.next();
		java.sql.Date sdate=rs.getDate(2);
		Date date=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
		return new ClassDTO(id, date, rs.getString(3).charAt(0), getTeacher(rs.getInt(4)));
	}
	
	/**
	 * @return Lista wszystkich uczniow zarejestrowanyc w systemie
	 */
	public LinkedList<StudentDTO> getStudents() {
		_students.clear();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT nr_legitymacji, nazwisko, imie, adres, data_urodzenia, miejsce_urodzenia, klasy_id FROM Uczniowie";
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				int id=rs.getInt(1);
				java.sql.Date sdate=rs.getDate(5);
				Date date=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
				_students.add(new StudentDTO(rs.getString(2), rs.getString(3),id,rs.getString(4),date,rs.getString(6),getAttendants(id), getExactClass(rs.getInt(7))));
			}
		} catch (Exception e) {
//			System.err.println(e.getMessage());
			return null;
		}
		return _students;
	}

	/**
	 * @return lista wszystkich nauczycieli w systemie
	 */
	public LinkedList<TeacherDTO> getTeachers() {
		_teachers.clear();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT id, nazwisko, imie, adres, telefon_kontaktowy FROM Nauczyciele";
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				int id=rs.getInt(1);
				_teachers.add(new TeacherDTO(id, rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),getUser(Const.Attributes.TEACHER,id),getSubjectsForTeacher(id)));
			}
		} catch (Exception e) {
//			System.err.println(e.getMessage());
			return null;
		}
		return _teachers;
	}

	/**
	 * Zwraca liste uczniów będących pod opieką danego opiekuna
	 * @param id identyfikator opiekuna
	 * @return lista uczniów
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	private LinkedList<StudentDTO> getStudentsForAttendant(int id) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		LinkedList<StudentDTO> lista=new LinkedList<StudentDTO>();
		getConnection();
		Statement stmt=connection.createStatement();
		String query="SELECT uczen_nazwisko, uczen_imie, nr_legitymacji, uczen_adres, uczen_data_urodzenia, uczen_miejsce_urodzenia, klasy_id FROM UczniowieZOpiekunami WHERE opiekun_id="+id;
		ResultSet rs=stmt.executeQuery(query);
		while(rs.next()) {
			int uid=rs.getInt(3);
			java.sql.Date sdate=rs.getDate(5);
			Date date=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
			lista.add(new StudentDTO(rs.getString(1),rs.getString(2),uid,rs.getString(4),date,rs.getString(6),null,getExactClass(rs.getInt(7))));
		}
		return lista;
	}
	
	/**
	 * Funkcja powinna zwrocic liste wszystkich opiekunow
	 * 
	 * @return lista opiekunow
	 */
	public LinkedList<AttendantDTO> getAttendants() {
		_attendants.clear();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT id, nazwisko, imie, adres, telefon, zaklad_pracy FROM Opiekunowie";
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				int id=rs.getInt(1);
				_attendants.add(new AttendantDTO(id, rs.getString(3),rs.getString(2),rs.getString(4),rs.getString(5),rs.getString(6),getUser(Const.Attributes.ATTENDANT,id),getStudentsForAttendant(id)));
			}
		} catch (Exception e) {
			System.err.println(e.getMessage());
			return null;
		}
		return _attendants;
	}

	
	/**
	 * Funkcja sluzaca do logowania
	 * 
	 * @param login
	 *            string z loginem
	 * @param pass
	 *            string z haslem
	 * @return Obiekt SystemUserDTO, może byc: 
	 * 			- TeacherDTO - kiedy zaloguje sie wychowawca 
	 * 			- AttendantDTO - kiedy zaloguje sie opiekun 
	 * 			- SystemUserDTO - kiedy sekretarka lub nieznany uzytkownik 
	 * 			Wazne, zeby wypelnic pole UserDTO user loginem i atrybutami - na ich
	 *         	podstawie rzutuje po stronie klienta na odpowiedni typ
	 */
	public SystemUserDTO getSystemUser(String login, String pass) {
		Const.Attributes attr=getAttributes(login,pass);
		System.out.println("Loguje sie "+attr);
		SystemUserDTO user=null;
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			if(attr==Const.Attributes.TEACHER || attr==Const.Attributes.EDUCATOR) {
				String query="SELECT nauczyciele_id FROM UzytkownicySystemu WHERE login='"+login+"'";
				ResultSet rs=stmt.executeQuery(query);
				rs.next();
				int id=rs.getInt(1);
				user=getTeacher(id);
				if(isEducator((TeacherDTO)user)) {
					user.user.attr=Const.Attributes.EDUCATOR;
				}
				return user;
			} else if(attr==Const.Attributes.ATTENDANT) {
				String query="SELECT opiekunowie_id FROM UzytkownicySystemu WHERE login='"+login+"'";
				ResultSet rs=stmt.executeQuery(query);
				rs.next();
				int id=rs.getInt(1);
				user=getAttendant(id);
				return user;
			} else if(attr==Const.Attributes.STUDENT) {
				//
				// Uczen nie jest uzytkownikiem systemu
				//
//				String query="SELECT uczniowie_nr_legitymacji FROM UzytkownicySystemu WHERE login='"+login+"'";
//				ResultSet rs=stmt.executeQuery(query);
//				rs.next();
//				int id=rs.getInt(1);
//				user=getStudent(id);
			} else if(attr==Const.Attributes.SECRETARY) {
				user=new SystemUserDTO();
				String query="SELECT email FROM UzytkownicySystemu WHERE login='"+login+"'";
				ResultSet rs=stmt.executeQuery(query);
				rs.next();
				user.user=new UserDTO(login,null,rs.getString(1),Const.Attributes.SECRETARY);
				return user;
			} else {
				user=new SystemUserDTO();
				user.user=new UserDTO();
				user.user=new UserDTO(null,null,null,Const.Attributes.UNKNOWN);
				return user;
			}
		} catch (Exception e) {
			SystemUserDTO sud=new SystemUserDTO();
			sud.user=new UserDTO(null,null,null,Const.Attributes.UNKNOWN);
			return sud;
		}
		return null;
	}
	
	
	/**
	 * Funkcja powinna zwracac liste wszystkich nauczanych w szkole przedmiotow
	 * 
	 * @return lista przedmiotow
	 */
	public LinkedList<SubjectDTO> getSubjects() {
		LinkedList<SubjectDTO> subjects=new LinkedList<SubjectDTO>();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT id, nazwa FROM Przedmioty";
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next())
				subjects.add(new SubjectDTO(rs.getInt(1), rs.getString(2)));
		} catch (Exception e) {
//			System.err.println(e.getMessage());
			return null;
		}
		return subjects;
	}
	
	/**
	 * Zwraca liste ocen ucznia z danego przedmiotu
	 * 
	 * @param student
	 *            uczen
	 * @param subject
	 *            przedmiot
	 * @return lista ocen
	 */
	public LinkedList<MarkDTO> getSingleMarks(StudentDTO student,
			SubjectDTO subject) {
		LinkedList<MarkDTO> lista=new LinkedList<MarkDTO>();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT id, wartosc, data, waga FROM CzytelneOceny WHERE nr_legitymacji="+student.numer+" AND przedmioty_id="+subject.id;
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				java.sql.Date sdate=rs.getDate(3);
				Date date=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
				lista.add(new MarkDTO(rs.getInt(1), rs.getInt(2), date, rs.getInt(4), student, subject));
			}
		} catch (Exception e) {
//			System.err.println(e.getMessage());
			return null;
		}
		return lista;
	}
	
	/**
	 * Zwraca ocene koncowa ucznia z danego przedmiotu
	 * 
	 * @param student
	 *            uczen
	 * @param subject
	 *            przedmiot
	 * @return ocena koncowa, jesli nie ma - null
	 */
	public MarkDTO getFinalMark(StudentDTO student, SubjectDTO subject) {
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT id, wartosc, data FROM CzytelneOcenyKoncowe WHERE nr_legitymacji="+student.numer+" AND przedmioty_id="+subject.id;
			ResultSet rs=stmt.executeQuery(query);
			rs.next();
			java.sql.Date sdate=rs.getDate(3);
			Date date=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
			return new MarkDTO(rs.getInt(1), rs.getInt(2), date, 0, student, subject);
		} catch (Exception e) {
//			System.err.println(e.getMessage());
			return null;
		}
	}
	
	/**
	 * Zwraca nauczyciela nauczajacego przedmiot subject w podanej klasie clas
	 * 
	 * @param subject
	 *            przedmiot
	 * @param clas
	 *            klasa
	 * @return nauczyciel
	 */
	public TeacherDTO getSubjectTeacherForClass(SubjectDTO subject,
			StudentDTO clas) {
		TeacherDTO nauczyciel=null;
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT nauczyciele_id FROM CzytelneOceny WHERE nr_legitymacji="+clas.numer+" AND przedmioty_id="+subject.id;
			ResultSet rs=stmt.executeQuery(query);
			rs.next();
			nauczyciel=getTeacher(rs.getInt(1));
		} catch (Exception e) {
			return null;
		}
		return nauczyciel;
	}
	
	/**
	 * Rejestracja ucznia
	 * 
	 * @param student
	 *            Uczen, ktory ma byc dodany do systemu
	 * @param attendants
	 *            Lista opiekunow, ktorzy sa zwiazani z tym uczniem
	 * @return Kod bledu
	 */
	public Const.Error.StudentRegisterMsg registerStudent(StudentDTO student) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.StudentRegisterMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("INSERT INTO Uczniowie (nr_legitymacji, imie, nazwisko, adres, data_urodzenia, miejsce_urodzenia, klasy_id) VALUES (");
		query.append(student.numer+", ");
		query.append("'"+student.name+"', ");
		query.append("'"+student.surname+"', ");
		query.append("'"+student.address+"', ");
		query.append("'"+(student.birth.getYear()+1900)+"-"+student.birth.getMonth()+"-"+student.birth.getDate()+"', ");
		query.append("'"+student.birthPlace+"', ");
		query.append(student.clas.id);
		query.append(")");
		System.out.println(query.toString());
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
//			if(rowCount == 0) {
//				return Const.Error.StudentRegisterMsg.UNKNOWN;
//			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			System.err.println(e.getSQLState());
			if(e.getSQLState().equals("23505"))
				return Const.Error.StudentRegisterMsg.STUDENT_ALREADY_EXISTS;
			return Const.Error.StudentRegisterMsg.UNKNOWN;
		}
		for(AttendantDTO attendant : student.attendants)
			try {
				//if(
				connectStudentWithAttendant(student,attendant);
				//< 1)
				//	return Const.Error.StudentRegisterMsg.ATTENDANT_ALREADY_EXISTS;
			} catch (Exception e) {
				System.out.println("Wystąpił błąd przy łączeniu ucznia z opiekunem: "+e.getMessage());
				return Const.Error.StudentRegisterMsg.UNKNOWN;
			}
		return Const.Error.StudentRegisterMsg.OK;
	}
	
	/**
	 * Usuwa ucznia
	 * 
	 * @param student
	 *            uczen, ktory ma byc usuniety
	 * @return Kod bledu
	 */
	public StudentRemoveMsg removeStudent(StudentDTO student) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.StudentRemoveMsg.CONNECTION_ERROR;
		}
		String query="DELETE FROM UczniowieOpiekunowie WHERE Uczniowie_nr_legitymacji="+student.numer;
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
//			if(rowCount == 0) {
//				return Const.Error.StudentRemoveMsg.UNKNOWN;
//			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return Const.Error.StudentRemoveMsg.UNKNOWN;
		}
		query="DELETE FROM UzytkownicySystemu WHERE Uczniowie_nr_legitymacji="+student.numer;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
//			if(rowCount == 0) {
//				return Const.Error.StudentRemoveMsg.UNKNOWN;
//			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return Const.Error.StudentRemoveMsg.UNKNOWN;
		}
		query="DELETE FROM Uczniowie WHERE nr_legitymacji="+student.numer;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
			if(rowCount == 0) {
				return Const.Error.StudentRemoveMsg.UNKNOWN;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return Const.Error.StudentRemoveMsg.UNKNOWN;
		}
		return Const.Error.StudentRemoveMsg.OK;
	}
	
	/**
	 * Ustala relacje pomiędzy studentem, a opiekunem w bazie
	 * @param student
	 * @param attendant
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws SQLException 
	 */
	private int connectStudentWithAttendant(StudentDTO student, AttendantDTO attendant) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		String query="INSERT INTO UczniowieOpiekunowie (Uczniowie_nr_legitymacji, Opiekun_id) VALUES ("+student.numer+", "+attendant.id+")";
		System.out.println(query);
		return stmt.executeUpdate(query.toString());
	}
	
	/**
	 * Modyfikuje dane ucznia
	 * 
	 * @param student
	 * @return
	 */
	public StudentModifyMsg modifyStudent(StudentDTO student) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.StudentModifyMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("UPDATE Uczniowie SET data_urodzenia=");
		query.append("'"+(student.birth.getYear()+1900)+"-"+student.birth.getMonth()+"-"+student.birth.getDate()+"', ");
		query.append("imie=");
		query.append("'"+student.name+"', ");
		query.append("nazwisko=");
		query.append("'"+student.surname+"', ");
		query.append("adres=");
		query.append("'"+student.address+"', ");
		query.append("miejsce_urodzenia=");
		query.append("'"+student.birthPlace+"', ");
		query.append("klasy_id=");
		query.append(student.clas.id);
		query.append(" WHERE nr_legitymacji="+student.numer);
		System.out.println(query.toString());
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
			if(rowCount == 0)
				return Const.Error.StudentModifyMsg.UNKNOWN;
		} catch (SQLException e) {
			return Const.Error.StudentModifyMsg.UNKNOWN;
		}
		return Const.Error.StudentModifyMsg.OK;
	}
	
	@Override
	public ClassDTO getEducatorsClass(TeacherDTO educator) {
		ClassDTO klasa=null;
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT klasy_id FROM Wychowawcy WHERE nauczyciele_id="+educator.id;
			ResultSet rs=stmt.executeQuery(query);
			rs.next();
			klasa=getExactClass(rs.getInt(1));
		} catch (Exception e) {
			return null;
		}
		return klasa;
	}

	
	/**
	 * Dodaje opiekuna
	 * 
	 * @param attendant
	 *            opiekun
	 * @return kod bledu
	 */
	public AttendantRegisterMsg registerAttendant(AttendantDTO attendant) {
		try {
			getConnection();
			connection.setAutoCommit(false);
		} catch (Exception e) {
			return Const.Error.AttendantRegisterMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("INSERT INTO Opiekunowie (imie, nazwisko, adres, telefon, zaklad_pracy) VALUES (");
		query.append("'"+attendant.name+"', ");
		query.append("'"+attendant.surname+"', ");
		query.append("'"+attendant.address+"', ");
		query.append("'"+attendant.telephone+"', ");
		query.append("'"+attendant.work+"'");
		query.append(")");
		System.out.println(query.toString());
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
			if(rowCount == 0) {
				return Const.Error.AttendantRegisterMsg.UNKNOWN;
			}
			ResultSet rs=stmt.executeQuery("SELECT id FROM Opiekunowie WHERE imie='"+attendant.name+"' AND nazwisko='"+attendant.surname+"' AND adres='"+attendant.address+"' AND telefon='"+attendant.telephone+"'");
			rs.next();
			attendant.id=rs.getInt(1);
			if(registerUser(Const.Attributes.ATTENDANT,rs.getInt(1),attendant.user) == 0) {
				System.err.println("Nie powiodło się registerUser");
				return Const.Error.AttendantRegisterMsg.UNKNOWN;
			}
			connection.commit();
			connection.setAutoCommit(true);
			registerAttendance(attendant);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			System.err.println(e.getSQLState());
			if(e.getSQLState().equals("23505"))
				return Const.Error.AttendantRegisterMsg.ATTENDANT_ALREADY_EXISTS;
			return Const.Error.AttendantRegisterMsg.UNKNOWN;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Const.Error.AttendantRegisterMsg.OK;
	}
	
	/**
	 * Funkcja kodujaca w sha1
	 * @param pass haslo
	 * @return sha1val
	 */
	private String sha1(String pass) {
		String sha1val = "";
        MessageDigest algorithm = null;
        try
        {
            algorithm = MessageDigest.getInstance("SHA-1");
        }
        catch (NoSuchAlgorithmException nsae)
        {
        	return null;
        }
        byte[] defaultBytes = pass.getBytes();
        algorithm.reset();
        algorithm.update(defaultBytes);
        byte messageDigest[] = algorithm.digest();
        StringBuffer hexString = new StringBuffer();
        for (int i = 0; i < messageDigest.length; i++)
        {
            String hex = Integer.toHexString(0xFF & messageDigest[i]);
            if (hex.length() == 1)
            {
                hexString.append('0');
            }
            hexString.append(hex);
        }
        sha1val = hexString.toString();
        return sha1val;
	}
	
	/**
	 * Rejestruje użytkownika w systemie
	 * @param attr uprawnienia
	 * @param id identyfikator
	 * @param user obiekt UserDTO z wypelnionymi polami login i pass
	 * @return
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	private int registerUser(Const.Attributes attr, int id, UserDTO user) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		StringBuilder query=new StringBuilder("");
		if(attr==Const.Attributes.ATTENDANT) {
			query.append("INSERT INTO UzytkownicySystemu (login, haslo, email, uprawnienia, opiekunowie_id) VALUES (");
			query.append("'"+user.login+"', ");
			query.append("'"+sha1(user.pass)+"', ");
			query.append("'"+user.email+"', 'opiekun', ");
			query.append(id);
			query.append(")");
		} else if(attr==Const.Attributes.TEACHER) {
			query.append("INSERT INTO UzytkownicySystemu (login, haslo, email, uprawnienia, nauczyciele_id) VALUES (");
			query.append("'"+user.login+"', ");
			query.append("'"+sha1(user.pass)+"', ");
			query.append("'"+user.email+"', 'nauczyciel', ");
			query.append(id);
			query.append(")");
		} else {
			return 0;
		}
		return stmt.executeUpdate(query.toString());
	}

	/**
	 * Modyfikuje opiekuna
	 * 
	 * @param attendant
	 *            opiekun
	 * @return kod bledu
	 */
	public AttendantModifyMsg modifyAttendant(AttendantDTO attendant) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.AttendantModifyMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("UPDATE Opiekunowie SET zaklad_pracy=");
		query.append("'"+attendant.work+"', ");
		query.append("imie=");
		query.append("'"+attendant.name+"', ");
		query.append("nazwisko=");
		query.append("'"+attendant.surname+"', ");
		query.append("adres=");
		query.append("'"+attendant.address+"', ");
		query.append("telefon=");
		query.append("'"+attendant.telephone+"'");
		query.append(" WHERE id="+attendant.id);
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
			if(rowCount == 0)
				return Const.Error.AttendantModifyMsg.UNKNOWN;
		} catch (SQLException e) {
			return Const.Error.AttendantModifyMsg.UNKNOWN;
		}
		return Const.Error.AttendantModifyMsg.OK;
	}

	/**
	 * Usuwa opiekuna
	 * 
	 * @param attendant
	 *            opiekun
	 * @return kod bledu
	 */
	public AttendantRemoveMsg removeAttendant(AttendantDTO attendant) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.AttendantRemoveMsg.CONNECTION_ERROR;
		}
		Statement stmt;
		String query="DELETE FROM UzytkownicySystemu WHERE Opiekunowie_id="+attendant.id;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
//			if(rowCount == 0) {
//				System.out.println("Nie powiodło się usuwanie opiekuna.");
//				return Const.Error.AttendantRemoveMsg.UNKNOWN;
//			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return Const.Error.AttendantRemoveMsg.UNKNOWN;
		} 
		
		query="DELETE FROM UczniowieOpiekunowie WHERE Opiekun_id="+attendant.id;
//		System.out.println(query);
		
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
//			if(rowCount == 0) {
//				return Const.Error.StudentRemoveMsg.UNKNOWN;
//			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return Const.Error.AttendantRemoveMsg.UNKNOWN;
		}
		
		
		query="DELETE FROM Opiekunowie WHERE id="+attendant.id;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
			//if(rowCount == 0) {
			//	return Const.Error.AttendantRemoveMsg.UNKNOWN;
			//}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return Const.Error.AttendantRemoveMsg.UNKNOWN;
		}
		return Const.Error.AttendantRemoveMsg.OK;
	}

	/**
	 * Modyfikuje dane nauczyciela
	 * 
	 * @param teacher
	 * @return
	 */
	public TeacherModifyMsg modifyTeacher(TeacherDTO teacher) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.TeacherModifyMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("UPDATE Nauczyciele SET ");
		query.append("imie=");
		query.append("'"+teacher.name+"', ");
		query.append("nazwisko=");
		query.append("'"+teacher.surname+"', ");
		query.append("adres=");
		query.append("'"+teacher.address+"', ");
		query.append("telefon_kontaktowy=");
		query.append("'"+teacher.telephone+"'");
		query.append(" WHERE id="+teacher.id);
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
			if(rowCount == 0)
				return Const.Error.TeacherModifyMsg.UNKNOWN;
		} catch (SQLException e) {
			return Const.Error.TeacherModifyMsg.UNKNOWN;
		}
		return Const.Error.TeacherModifyMsg.OK;
	}

	/**
	 * Dodaje nauczyciela do systemu
	 * 
	 * @param teacher
	 * @return
	 */
	public TeacherRegisterMsg registerTeacher(TeacherDTO teacher) {
		try {
			getConnection();
			connection.setAutoCommit(false);
		} catch (Exception e) {
			return Const.Error.TeacherRegisterMsg.CONNECTION_ERROR;
		}
		StringBuilder query=new StringBuilder("INSERT INTO Nauczyciele (imie, nazwisko, adres, telefon_kontaktowy) VALUES (");
		query.append("'"+teacher.name+"', ");
		query.append("'"+teacher.surname+"', ");
		query.append("'"+teacher.address+"', ");
		query.append("'"+teacher.telephone+"'");
		query.append(")");
		//System.out.println(query.toString());
		Statement stmt;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query.toString());
			//connection.close();
			//connection=null;
			if(rowCount == 0) {
				return Const.Error.TeacherRegisterMsg.UNKNOWN;
			}
			ResultSet rs=stmt.executeQuery("SELECT id FROM Nauczyciele WHERE imie='"+teacher.name+"' AND nazwisko='"+teacher.surname+"' AND adres='"+teacher.address+"' AND telefon_kontaktowy='"+teacher.telephone+"'");
			rs.next();
			teacher.id=rs.getInt(1);
			if(registerUser(Const.Attributes.TEACHER,rs.getInt(1),teacher.user) == 0) {
				return Const.Error.TeacherRegisterMsg.UNKNOWN;
			}
			connection.commit();
			connection.setAutoCommit(true);
			registerSubjects(teacher);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			System.err.println(e.getSQLState());
			if(e.getSQLState().equals("23505"))
				return Const.Error.TeacherRegisterMsg.TEACHER_ALREADY_EXISTS;
			return Const.Error.TeacherRegisterMsg.UNKNOWN;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return Const.Error.TeacherRegisterMsg.OK;
	}
	
	/**
	 * Usuwa nauczyciela z systemu
	 * 
	 * @param teacher
	 * @return
	 */
	public TeacherRemoveMsg removeTeacher(TeacherDTO teacher) {
		try {
			getConnection();
		} catch (Exception e) {
			return Const.Error.TeacherRemoveMsg.CONNECTION_ERROR;
		}
		Statement stmt;
		String query="DELETE FROM UzytkownicySystemu WHERE Nauczyciele_id="+teacher.id;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
//			if(rowCount == 0) {
//				return Const.Error.StudentRemoveMsg.UNKNOWN;
//			}
		} catch (SQLException e) {
//			System.out.println(e.getMessage());
			return Const.Error.TeacherRemoveMsg.UNKNOWN;
		} 
		
		query="DELETE FROM NauczanePrzedmioty WHERE Nauczyciele_id="+teacher.id;
//		System.out.println(query);
		
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
//			if(rowCount == 0) {
//				return Const.Error.StudentRemoveMsg.UNKNOWN;
//			}
		} catch (SQLException e) {
//			System.out.println(e.getMessage());
			return Const.Error.TeacherRemoveMsg.UNKNOWN;
		}
		
		
		query="DELETE FROM Nauczyciele WHERE id="+teacher.id;
		try {
			stmt = connection.createStatement();
			int rowCount=stmt.executeUpdate(query);
			//connection.close();
			//connection=null;
			if(rowCount == 0) {
				return Const.Error.TeacherRemoveMsg.UNKNOWN;
			}
		} catch (SQLException e) {
//			System.out.println(e.getMessage());
			return Const.Error.TeacherRemoveMsg.UNKNOWN;
		}
		return Const.Error.TeacherRemoveMsg.OK;
	}
	
	private void registerSubjects(TeacherDTO teacher) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		for(SubjectDTO subject : teacher.subjects) {
			Statement stmt=connection.createStatement();
			String query="INSERT INTO NauczanePrzedmioty (Nauczyciele_id, Przedmioty_id) VALUES ("+teacher.id+", "+subject.id+")";
			int rowcount=stmt.executeUpdate(query);
		}
	}
	
	private void registerAttendance(AttendantDTO attendant) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		for(StudentDTO student : attendant.students) {
			Statement stmt=connection.createStatement();
			String query="INSERT INTO UczniowieOpiekunowie (Opiekun_id, Uczniowie_nr_legitymacji) VALUES ("+attendant.id+", "+student.numer+")";
			//System.out.println(query);
			int rowcount=stmt.executeUpdate(query);
		}
	}
	
	/**
	 * Dodaje ocene jednostkowa
	 * 
	 * @param mark
	 *            ocena
	 * @return czy sie udalo
	 */
	public Boolean addSingleMark(MarkDTO mark, TeacherDTO teacher) {
		try {
			getConnection();
			Statement stmt;
			int nid=getTaughtSubjectId(mark.subject,teacher);
			stmt=connection.createStatement();
			StringBuilder query=new StringBuilder("INSERT INTO Oceny (wartosc, data, waga, uczniowie_nr_legitymacji, nauczane_przedmioty_id) VALUES (");
			query.append(mark.value+", ");
			query.append("'"+(mark.date.getYear()+1900)+"-"+mark.date.getMonth()+"-"+mark.date.getDate()+"', ");
			query.append(mark.weight+", ");
			query.append(mark.student.numer+", ");
			query.append(nid);
			query.append(")");
			if(stmt.executeUpdate(query.toString()) == 0)
				return false;
		} catch (Exception e) {
			return false;
		}
		return true;
	}
	
	private int getTaughtSubjectId(SubjectDTO subject, TeacherDTO teacher) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		getConnection();
		Statement stmt=connection.createStatement();
		String query="SELECT id FROM NauczanePrzedmioty WHERE nauczyciele_id="+teacher.id+" AND przedmioty_id="+subject.id;
		ResultSet rs=stmt.executeQuery(query);
		rs.next();
		return rs.getInt(1);
	}

	/**
	 * Usuwa ocene jednostkowa
	 * 
	 * @param mark
	 *            ocena, w niej jest id, wystarczy w bazie poszukac po tym id
	 * @return czy sie udalo
	 */
	public Boolean removeSingleMark(MarkDTO mark) {
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="DELETE FROM Oceny WHERE id="+mark.id;
			if(stmt.executeUpdate(query) == 0)
				return false;
		} catch (Exception e) {
			return false;
		}
		return true;
	}

	/**
	 * Usuwa ocene koncowa
	 * 
	 * @param mark
	 *            ocena
	 * @return czy sie udalo
	 */
	public Boolean removeFinalMark(MarkDTO mark) {
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="DELETE FROM OcenyKoncowe WHERE id="+mark.id;
			if(stmt.executeUpdate(query) == 0)
				return false;
		} catch (Exception e) {
			return false;
		}
		return true;
	}


	/**
	 * Ustala ocene koncowa, w srodku jest dla kogo i z czego
	 * 
	 * @param mark
	 *            ocena
	 * @return czy sie udalo
	 */
	public Boolean setFinalMark(MarkDTO mark, TeacherDTO teacher) {
		try {
			getConnection();
			Statement stmt;
			int nid=getTaughtSubjectId(mark.subject,teacher);
			stmt=connection.createStatement();
			StringBuilder query=new StringBuilder("INSERT INTO OcenyKoncowe (wartosc, data, nr_legitymacji, nauczane_przedmioty_id) VALUES (");
			query.append(mark.value+", ");
			query.append("'"+(mark.date.getYear()+1900)+"-"+mark.date.getMonth()+"-"+mark.date.getDate()+"', ");
			query.append(mark.student.numer+", ");
			query.append(nid);
			query.append(")");
			if(stmt.executeUpdate(query.toString()) == 0)
				return false;
		} catch (Exception e) {
			return false;
		}
		return true;
	}


	/**
	 * Dodaje nieobecnosc
	 * 
	 * @param absence nieobecnosc
	 * @return kod bledu
	 */
	public AbsenceRegisterMsg addAbsence(AbsenceDTO absence) {
		try {
			getConnection();
			Statement stmt;
			stmt=connection.createStatement();
			StringBuilder query=new StringBuilder("INSERT INTO Nieobecnosci (start, koniec, czy_usprawiedliwiona, uczniowie_nr_legitymacji) VALUES (");
			query.append("'"+(absence.start.getYear()+1900)+"-"+absence.start.getMonth()+"-"+absence.start.getDate()+"', ");
			query.append("'"+(absence.stop.getYear()+1900)+"-"+absence.stop.getMonth()+"-"+absence.stop.getDate()+"', ");
			query.append("'"+absence.excused+"', ");
			query.append(absence.student.numer);
			query.append(")");
			//System.out.println(query.toString());
			if(stmt.executeUpdate(query.toString()) == 0)
				return Const.Error.AbsenceRegisterMsg.UNKNOWN;
		} catch(SQLException e) {
			System.out.println(e.getMessage());
			if(e.getSQLState().equals("23513"))
				return Const.Error.AbsenceRegisterMsg.WRONG_DATE;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return Const.Error.AbsenceRegisterMsg.UNKNOWN;
		}
		return Const.Error.AbsenceRegisterMsg.OK;
	}


	/**
	 * Usprawiedliwia/cofa usprawiedliwienie dla nieobecnosci
	 * 
	 * @param absence nieobecnosc
	 * @return czy sie udalo
	 */
	public Boolean excuseAbsence(AbsenceDTO absence) {
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT czy_usprawiedliwiona FROM Nieobecnosci WHERE id="+absence.id;
			ResultSet rs=stmt.executeQuery(query);
			rs.next();
			boolean usprawiedliwiona=rs.getBoolean(1);
			if(usprawiedliwiona)
				query="UPDATE Nieobecnosci SET czy_usprawiedliwiona='false' WHERE id="+absence.id;
			else
				query="UPDATE Nieobecnosci SET czy_usprawiedliwiona='true' WHERE id="+absence.id;
			if(stmt.executeUpdate(query) == 0)
				return false;
		} catch (Exception e) {
			return false;
		}
		return true;
	}


	/**
	 * Zwraca liste nieobecnosci ucznia
	 * 
	 * @param student uczen
	 * @return lista nieobecnosci
	 */
	public LinkedList<AbsenceDTO> getAbsences(StudentDTO student) {
		LinkedList<AbsenceDTO> lista=new LinkedList<AbsenceDTO>();
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="SELECT id, start, koniec, czy_usprawiedliwiona FROM Nieobecnosci WHERE Uczniowie_nr_legitymacji="+student.numer;
			ResultSet rs=stmt.executeQuery(query);
			while(rs.next()) {
				java.sql.Date sdate=rs.getDate(2);
				Date date1=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
				sdate=rs.getDate(3);
				Date date2=new Date(sdate.getYear(),sdate.getMonth(),sdate.getDay());
				lista.add(new AbsenceDTO(student, rs.getInt(1), date1, date2, rs.getBoolean(4)));
			}
		} catch (Exception e) {
			System.err.println(e.getMessage());
			return null;
		}
		return lista;
	}


	/**
	 * Usuwa nieobecnosc
	 * 
	 * @param absence nieobecnosc
	 * @return czy sie udalo
 	 */
	public Boolean removeAbsence(AbsenceDTO absence) {
		try {
			getConnection();
			Statement stmt=connection.createStatement();
			String query="DELETE FROM Nieobecnosci WHERE id="+absence.id;
			if(stmt.executeUpdate(query) == 0)
				return false;
		} catch (Exception e) {
			return false;
		}
		return true;
	}

}
	